﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace LRS.DAL
{
    static class SqlHelper
    {
        public static SqlConnection connection;
        private static SqlCommand command;

        static SqlHelper()
        {
            connection = new SqlConnection(DefaultSetting.Default.ConnectionString);
            command = new SqlCommand();
            command.Connection = connection;
        }

        public static void ExecuteNonQuery(string query)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
        public static SqlDataReader ExecuteReader(string query)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public static void ExecuteProcedure(string ProcedureName, List<SqlParameter> parameters)
        {
            command.CommandText = ProcedureName;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Clear();
            foreach (var param in parameters)
            {
                command.Parameters.Add(param);
            }
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
        public static SqlDataReader ExecuteReader(string query, List<SqlParameter> parameters)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            command.Parameters.Clear();
            foreach (var param in parameters)
            {
                command.Parameters.Add(param);
            }
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            SqlDataReader reader= command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        public static void ExecuteNonQuery(string query, List<SqlParameter> parameters)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            if (parameters != null)
            {
                command.Parameters.Clear();
                foreach (var param in parameters)
                    command.Parameters.Add(param);
            }
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}
